package com.student.management.root.dao.impl;

import com.student.management.root.bean.Role;
import com.student.management.root.bean.Users;
import com.student.management.root.dao.UsersDao;
import com.student.management.root.utils.DBUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UsersDaoImpl extends DBUtils implements UsersDao {
    @Override
    public Users login(String username, String password) {
        Users users = null;
        String sql = "SELECT * FROM users as u WHERE 1=1 AND loginname = ? AND u.`isdelete` = 0 AND PASSWORD = ?;";
        List params = new ArrayList<>();
        params.add(username);
        params.add(password);
        resultSet = query(sql, params);
        if(resultSet==null){
            return null;
        }
        try {
            while (resultSet.next()){
                users = new Users();
                users.setLoginName(username);
                users.setRealName(resultSet.getString("realname"));
                users.setUserId(resultSet.getInt("userid"));
                users.setRoleId(resultSet.getInt("roleid"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return users;
    }

    @Override
    public List<Users> getUsersList(int pageIndex, int pageSize) {
        ArrayList<Users> usersArrayList = new ArrayList<Users>();
        String sql = "select userid,loginname,realname,rolename from users u,role r where u.roleid=r.roleid and u.`isdelete` = 0  limit ?,?;";
        ArrayList<Object> params = new ArrayList<>();
        params.add((pageIndex-1)*pageSize);
        params.add(pageSize);
        resultSet = query(sql, params);
        if (resultSet == null) {
            return null;
        }
        try {
            while (resultSet.next()){
                //1.取出各表的数据
                Users users = new Users();
                users.setUserId(resultSet.getInt("userid"));
                users.setLoginName(resultSet.getString("loginname"));
                users.setRealName(resultSet.getString("realname"));

                Role role = new Role();
                role.setRolename(resultSet.getString("rolename"));
                users.setRole(role);
                usersArrayList.add(users);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return usersArrayList;
    }

    @Override
    public int total() {
        int total=0;
        try {
            String sql="select count(1) from users u,role r where u.roleid=r.roleid  and u.`isdelete` = 0  ";


            resultSet = query(sql, null);
            while(resultSet.next()){
                total=resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll();
        }
        return total;
    }

    @Override
    public int insertUser(Users user) {
        int i =0;
        try {
            String sql="insert into users values(null,?,?,?,?,?,?,?,?,?,0,?)";
            List params=new ArrayList();
            params.add(user.getLoginName());
            params.add(user.getPassWord());
            params.add(user.getRealName());
            params.add(user.getSex());
            params.add(user.getEmail());
            params.add(user.getAddress());
            params.add(user.getPhone());
            params.add(user.getCardId());
            params.add(user.getDesc());
            params.add(user.getRoleId());
            System.out.println(params.toString());
            i = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public int updateUser(Users user) {
        int update = 0;
        try {
            //这里需要大家自己完善非主键列以外的列的更新
            String sql="UPDATE users SET loginname=?,users.`password`=?,realname=?,sex=?,email=?,address=?,phone=?,cardid=?,users.`desc`=?,roleid=? WHERE userid=?;";
            List params=new ArrayList();
            params.add(user.getLoginName());
            params.add(user.getPassWord());
            params.add(user.getRealName());
            params.add(user.getSex());
            params.add(user.getEmail());
            params.add(user.getAddress());
            params.add(user.getPhone());
            params.add(user.getCardId());
            params.add(user.getDesc());
            params.add(user.getRoleId());
            params.add(user.getUserId());

            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public Users findById(int userId) {
        Users users = new Users();
        try {
            String sql="select * from users as u where userid=?  and u.`isdelete` = 0 ";
            List params=new ArrayList();
            params.add(userId);

            resultSet=query(sql,params);
            while(resultSet.next()){
                users.setUserId(resultSet.getInt("userid"));
                users.setAddress(resultSet.getString("address"));
                users.setLoginName(resultSet.getString("loginname"));
                users.setPassWord(resultSet.getString("realname"));
                users.setRealName(resultSet.getString("realname"));
                users.setSex(resultSet.getInt("sex"));
                users.setEmail(resultSet.getString("email"));
                users.setPhone(resultSet.getString("phone"));
                users.setCardId(resultSet.getString("cardid"));
                users.setDesc(resultSet.getString("desc"));
                users.setRoleId(resultSet.getInt("roleid"));

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return users;
    }

    @Override
    public int deleteById(Integer userId) {
        int update = 0;
        try {
            //伪删除
            String sql="UPDATE users as u SET u.`isdelete`=1 WHERE userid=?;";
            List params=new ArrayList();
            params.add(userId);

            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }
}
